﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Codebricks.Db;
using KBVault.DAL.Entities;
using System.Data;
using KBVault.DAL.Exceptions;

namespace KBVault.DAL.Repository
{
    public class TagRepository:BaseRepository
    {
        private static string CommandListArticlesByTag = "Select distinct article_id from v_article_tags where tagname = @TagName limit {0},{1}";
        private static string CommandArticleCountByTag = "Select Count(distinct article_id) article_count from v_article_tags where tagname = @TagName";
        private static string CommandGetPopularTags = "select count(*) as frequency, t.name tag_name from article_Tags at left join tags t on at.tag_id = t.id group by t.name having count(*) > 0 order by count(*) desc limit 0,20";
        
        public static void Create(ref Tag a)
        {
            try
            {
                a.Id = (int)MySqlDb.Insert(a);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw;
            }
        }

        public static void Update(Tag a)
        {
            try
            {
                MySqlDb.Update(a);
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static void Delete(int id)
        {
            try
            {
                Tag a = new Tag() { Id = id };
                MySqlDb.Delete(a);
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static Tag Get(int id)
        {
            try
            {
                Tag a = new Tag() { Id = id };
                return MySqlDb.Get<Tag>(a);
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static Tag GetByName( string name )
        {
            try
            {
                Tag t = new Tag();
                MysqlParameterCollection col = new MysqlParameterCollection();
                col.Add("Name", name);
                DataTable dt = MySqlDb.Select("Select * from tags where name=@Name", col);
                if (dt.Rows.Count == 1)
                    return new Tag()
                    {
                        Id = Convert.ToInt32(dt.Rows[0]["id"]),
                        Name = name
                    };
                throw new NoDataFoundException("Tag <" + name + "> not found ");
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static int GetArticleCountWithTag( string tagName )
        {
            try
            {
                MysqlParameterCollection col = new MysqlParameterCollection();
                col.Add("TagName", tagName);
                DataTable dt = MySqlDb.Select(CommandArticleCountByTag, col);
                if (dt.Rows.Count == 1)
                {
                    return Convert.ToInt32(dt.Rows[0]["article_count"]);
                }
                return 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }
        public static List<Article> GetArticlesWithTag( string tagName, int page, int itemsPerPage )
        {
            try
            {
                int start = (page-1)*itemsPerPage;
                List<Article> articles = new List<Article>();
                MysqlParameterCollection col = new MysqlParameterCollection();
                col.Add("TagName", tagName);
                DataTable dt = MySqlDb.Select(String.Format(CommandListArticlesByTag, start, itemsPerPage),col);
                if (dt.Rows.Count > 0)
                {
                    foreach(DataRow row in dt.Rows)
                    {
                        articles.Add(ArticleRepository.Get(Convert.ToInt32(row["article_id"])));
                    }
                }
                return articles;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static List<Tag> GetSimilarTags( string tagName)
        {
            try
            {
                List<Tag> tags = new List<Tag>();
                MysqlParameterCollection col = new MysqlParameterCollection();
                col.Add("Name", "%"+tagName+"%");
                DataTable dt = MySqlDb.Select("Select id,name from tags where name like @Name limit 0,15", col);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        Tag t = new Tag { Id = Convert.ToInt32(row["id"]), Name = row["name"].ToString() };
                        tags.Add(t);
                    }
                }
                return tags;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static List<TagCloudItem> GetPopularTags()
        {
            try
            {
                List<TagCloudItem> tags = new List<TagCloudItem>();
                
                DataTable dt = MySqlDb.Select(CommandGetPopularTags,null);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        TagCloudItem t = new TagCloudItem { Count = Convert.ToInt32(row["frequency"]), Name = row["tag_name"].ToString() };
                        tags.Add(t);
                    }
                }
                return tags;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }
    }
}
